﻿using Root.Models.Structs;
using Root.Properties;
using SQLHelper;
using System;
using System.Data;

namespace Root.Models.DAL
{
    public class ArticleDAL
    {
        public static string connStr = Resources.SQLite_Root;
        public static SQLiteHelper SQL = new SQLiteHelper(connStr);
        public static SQLParameters.SQLite p = new SQLParameters.SQLite();
        public static long GetMAXCount()
        {
            try
            {
                return (long)SQL.ExecuteScalar("select Count from T_BBS order by Count desc limit 1");
            }
            catch (Exception)
            {
                return 0;
            }
        }
        public static Article[] GetTitleList()
        {
            DataTable dt = SQL.ExecuteDataTable("select * from T_BBS where Pid=0 order by Count desc");
            Article[] articles = new Article[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Article art = new Article();
                DataRow row = dt.Rows[i];
                art.Id = (long)row["Id"];
                art.Msg = (string)row["Msg"];
                art.Name = (string)row["Name"];
                art.IP = (string)row["IP"];
                art.Location = (string)row["Location"];
                art.Agent = (string)row["Agent"];
                art.Time = (string)row["Time"];
                art.Pid = (long)row["Pid"];
                art.Count = (long)row["Count"];
                articles[i] = art;
            }
            return articles;
        }

        public static Article[] GetListByPid(int pid)
        {
            DataTable dt = SQL.ExecuteDataTable("select * from T_BBS where Pid=@Pid order by Id asc", p.New("@Pid", pid));
            Article[] articles = new Article[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Article art = new Article();
                DataRow row = dt.Rows[i];
                art.Id = (long)row["Id"];
                art.Msg = (string)row["Msg"];
                art.Name = (string)row["Name"];
                art.IP = (string)row["IP"];
                art.Location = (string)row["Location"];
                art.Agent = (string)row["Agent"];
                art.Time = (string)row["Time"];
                art.Pid = (long)row["Pid"];
                art.Count = (long)row["Count"];
                articles[i] = art;
            }
            return articles;
        }

        public static Article GetArticleById(int id)
        {
            DataTable dt = SQL.ExecuteDataTable("select * from T_BBS where Id=@Id", p.New("@Id", id));
            if (dt.Rows.Count <= 0)
            {
                return new Article();
            }
            else if (dt.Rows.Count >= 2)
            {
                DataRow row = dt.Rows[0];
                Article article = new Article();
                article.Id = (long)row["Id"];
                article.Name = (string)row["Name"];
                article.Msg = (string)row["Msg"];
                article.IP = (string)row["IP"];
                article.Location = (string)row["Location"];
                article.Time = (string)row["Time"];
                article.Agent = (string)row["Agent"];
                return article;
            }
            else
            {
                DataRow row = dt.Rows[0];
                Article article = new Article();
                article.Id = (long)row["Id"];
                article.Name = (string)row["Name"];
                article.Msg = (string)row["Msg"];
                article.IP = (string)row["IP"];
                article.Location = (string)row["Location"];
                article.Time = (string)row["Time"];
                article.Agent = (string)row["Agent"];
                return article;
            }
        }

        public static string Insert(Article newArticle)
        {
            try
            {
                SQL.ExecuteNonQuery("insert into T_BBS(Name,Msg,Ip,Location,Agent,Time,Pid,Count) values(@Name,@Msg,@Ip,@Location,@Agent,@Time,@Pid,@Count)",
                    p.New("@Name", newArticle.Name),
                    p.New("@Msg", newArticle.Msg),
                    p.New("@IP", newArticle.IP),
                    p.New("@Location", newArticle.Location),
                    p.New("@Agent", newArticle.Agent),
                    p.New("@Time", newArticle.Time),
                    p.New("@Pid", newArticle.Pid),
                    p.New("@Count", newArticle.Count)
                    );
                return "OK";
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }

        public static string AddCount(int id)
        {
            try
            {
                SQL.ExecuteNonQuery("UPDATE T_BBS SET Count=Count+1 WHERE Id=@Id", p.New("@Id", id));
                return "OK";
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }
    }
}